#!/bin/bash

# Configure
host="disciplinas.dcc.ufba.br" # "localhost"   
user="caiosba"                 # "ufba"        
db="MATB09_caiosba"            # "matb09"      

# Destroy tables
psql -q -U $user -h $host -d $db -c "DROP TABLE companies CASCADE;
DROP TABLE branch_offices CASCADE;
DROP TABLE employees CASCADE;
DROP TABLE jobs CASCADE;
DROP TABLE hour_bank CASCADE;
DROP TABLE hours CASCADE;
DROP TABLE links CASCADE;
DROP TABLE branch_office_has_department CASCADE;
DROP TABLE holidays CASCADE;
DROP TABLE offs CASCADE;
DROP TABLE roles CASCADE;
DROP TABLE departments CASCADE;"

# Create language
createlang -U $user -h $host plpgsql $db

# Create tables
psql -q -U $user -h $host -d $db -f create.sql

# Create functions
psql -q -U $user -h $host -d $db -f functions.sql

# Add some data

# Create an employee
psql -q -U $user -h $host -d $db -c "
INSERT INTO roles                        (name)                            VALUES ('user');
INSERT INTO companies                    (name)                            VALUES ('UFBA');
INSERT INTO branch_offices               (company_id, city_name)           VALUES (1, 'Salvador');
INSERT INTO departments                  (name)                            VALUES ('DCC');
INSERT INTO branch_office_has_department (department_id, branch_office_id) VALUES (1, 1);
INSERT INTO jobs                         (name)                            VALUES ('Professor');
INSERT INTO employees 
  (name, login, password, hours, salary, genre, branch_office_id, job_id, department_id, role_id) VALUES 
  ('Daniela', 'daniela', '123456', 10, 10000, 'f', 1, 1, 1, 1);"

# Holidays
psql -q -U $user -h $host -d $db -c "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-04-02', 'Sexta-Feira Santa');"
psql -q -U $user -h $host -d $db -c "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-04-21', 'Tiradentes');"
psql -q -U $user -h $host -d $db -c "INSERT INTO holidays (branch_office_id, holiday_date, reason) VALUES (1, '2010-07-02', 'Independência da Bahia');"
psql -q -U $user -h $host -d $db -c "SELECT business_days('2010-04-01','2010-04-30') AS Dias_Úteis_em_Abril"
psql -q -U $user -h $host -d $db -c "SELECT holiday_days(1,4,2010) AS Feriados_no_Escritório;"
psql -q -U $user -h $host -d $db -c "SELECT bo_business_days(1,4,2010) AS Dias_Úteis_no_Escritório;"

# Expected hours in april, excluding offs
psql -q -U $user -h $host -d $db -c "SELECT employee_hours(1,4,2010) AS Horas_Previstas_em_Abril_sem_Descontar_Horas_Justificadas"

# Offs
psql -q -U $user -h $host -d $db -c "INSERT INTO offs (employee_id, hours, off_date, notes) VALUES (1, 1.7, '2010-04-08', 'Médico');"
psql -q -U $user -h $host -d $db -c "INSERT INTO offs (employee_id, hours, off_date, notes) VALUES (1, 2.3, '2010-04-20', 'Participação em Congresso');"
psql -q -U $user -h $host -d $db -c "SELECT off_hours(1,4,2010) AS Horas_Faltantes_Justificadas;"

# Create triggers
psql -q -U $user -h $host -d $db -f triggers.sql

# Employee worked 50h in april 2010
psql -q -U $user -h $host -d $db -c "DELETE FROM hours;
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-02 08:30:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-02 12:50:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-09 14:13:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-09 22:53:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'dentro', '2010-04-16 12:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-16 22:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-22 07:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-22 14:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-23 06:25:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-23 22:55:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-26 07:10:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-26 11:40:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'in', '2010-04-27 22:00:00');
INSERT INTO hours (employee_id, ip, in_or_out, time) VALUES (1, '127.0.0.1', 'out', '2010-04-28 1:00:00');
";

# How many hours did the employee work in  april?
psql -q -U $user -h $host -d $db -c "SELECT employee_hours(1,4,2010) AS Horas_Previstas_em_Abril;"
psql -q -U $user -h $host -d $db -c "SELECT float2hour(cast(worked_hours(1,4,2010) as decimal)) AS Horas_Trabalhadas_em_Abril;"

# Create views
psql -q -U $user -h $host -d $db -f views.sql

# Who is our employee?
psql -q -U $user -h $host -d $db -c "SELECT * FROM employee_info WHERE id = 1"

# Bonus hours for our employee
psql -q -U $user -h $host -d $db -c "SELECT * FROM extra_hours WHERE id = 1"

# Holidays for the branch office this year
psql -q -U $user -h $host -d $db -c "SELECT * FROM year_holidays WHERE branch_office_id = 1 "
